﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Xml;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class Binding
    {
        public Binding()
        {

        }
        public static Guid CreateMSEBinding(DTO.MSEBinding binding, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateBinding]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = binding.Name;
                    if (binding.Description != null)
                    {
                        DescriptionParam.Value = binding.Description;
                    }
                    else
                    {
                        DescriptionParam.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();

                    ID = (Guid)IDParam.Value;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }

            return ID;
        }

        public static DTO.MSEBinding GetMSEBinding(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.MSEBinding binding = new DTO.MSEBinding();
            List<DTO.MSEBindingElement> list = null;

            const string sprocname = "[ServiceEngine].[GetBinding]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.MSEBindingElement>();
                            while (reader.Read())
                            {
                                DTO.MSEBindingElement element = new DTO.MSEBindingElement(); 
                                binding.ID = reader.GetGuid(0);

                                binding.Name = reader.GetString(1);
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(2).CreateReader());
                                element.BindingElementConfig = xdoc.DocumentElement;
                                if (reader.IsDBNull(3))
                                {
                                    binding.Description = null;
                                }
                                else
                                {
                                    binding.Description = reader.GetString(3);
                                }
                                element.ElementOrder = reader.GetByte(4);
                                element.ID = reader.GetGuid(5);
                                element.ElementType = (DTO.BindingElementType)reader.GetInt16(6);


                                list.Add(element);
                            }
                            binding.BindingElements = list;
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }

            return binding;

        }

        public static List<DTO.MSEBindingElement> GetBindingElementsForBindingID(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.MSEBindingElement> list = null;

            const string sprocname = "[ServiceEngine].[GetBindingElementsForBindingID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.MSEBindingElement>();
                            while (reader.Read())
                            {
                                DTO.MSEBindingElement element = new DTO.MSEBindingElement();
                                element.ID = reader.GetGuid(1);

                                element.ElementOrder = Convert.ToInt32( reader.GetValue(2));
                                XmlDocument xdoc = new XmlDocument();
                                xdoc.Load(reader.GetSqlXml(3).CreateReader());
                                element.BindingElementConfig = xdoc.DocumentElement;
                                element.Name = reader.GetString(4);
                                if (reader.IsDBNull(5))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(5);
                                }
                                element.ElementType = (DTO.BindingElementType)reader.GetInt16(6);

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }

            return list;

        }

        public static void DeleteMSEBinding(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteBinding]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }

        }

        public static DTO.MSEBinding UpdateMSEBinding(DTO.MSEBinding binding, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateBinding]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = binding.ID;
                    NameParam.Value = binding.Name;
                    if (binding.Description != null)
                    {
                        DescriptionParam.Value = binding.Description;
                    }
                    else
                    {
                        DescriptionParam.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(DescriptionParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }

            return binding;
        }

        public static void AssignBindingElementsToBinding(Guid bindingID, Guid bindingElementID, int elementOrder, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignBindingElementsToBinding]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                //Note: No change tracking needed for newly activated operations at an endpoint
                //brokers will query for the operation to resolve since it won't be in the broker cache

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter BindingIDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter BindingElementIDParam = new SqlParameter("@BindingElementID", SqlDbType.UniqueIdentifier);
                    SqlParameter ElementOrderParam = new SqlParameter("@ElementOrder", SqlDbType.TinyInt);


                    cmd.CommandType = CommandType.StoredProcedure;
                    BindingIDParam.Value = bindingID;
                    BindingElementIDParam.Value = bindingElementID;
                    ElementOrderParam.Value = elementOrder;

                    cmd.Parameters.Add(BindingIDParam);
                    cmd.Parameters.Add(BindingElementIDParam);
                    cmd.Parameters.Add(ElementOrderParam);


                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }
        }

        public static void RemoveBindingElementsFromBinding(Guid bindingID, Guid bindingElementID, int elementOrder, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveBindingElementsFromBinding]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter BindingIDParam = new SqlParameter("@BindingID", SqlDbType.UniqueIdentifier);
                    SqlParameter BindingElementIDParam = new SqlParameter("@BindingElementID", SqlDbType.UniqueIdentifier);
                    SqlParameter ElementOrderParam = new SqlParameter("@ElementOrder", SqlDbType.TinyInt);


                    cmd.CommandType = CommandType.StoredProcedure;
                    BindingIDParam.Value = bindingID;
                    BindingElementIDParam.Value = bindingElementID;
                    ElementOrderParam.Value = elementOrder;

                    cmd.Parameters.Add(BindingIDParam);
                    cmd.Parameters.Add(BindingElementIDParam);
                    cmd.Parameters.Add(ElementOrderParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }
        }


        public static DTO.MSEBindingList ListMSEBindings(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.MSEBindingList list = new DTO.MSEBindingList();
            const string sprocname = "[ServiceEngine].[ListBindings]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.MSEBindings = new List<DTO.MSEBindingListElement>();
                            while (reader.Read())
                            {
                                DTO.MSEBindingListElement element = new DTO.MSEBindingListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.MSEBindings.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }


            return list;

        }

        public static List<DTO.MSEBindingListElement> ListMSEBindingsByGroup(Guid entityGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.MSEBindingListElement> list = new List<DTO.MSEBindingListElement>();
            const string sprocname = "[ServiceEngine].[ListEntitiesByGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = entityGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.MSEBindingListElement>();
                            while (reader.Read())
                            {
                                DTO.MSEBindingListElement element = new DTO.MSEBindingListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }

            return list;

        }

        public static DTO.MSEBindingList ListMSEBindingsForBindingElementID(Guid bindingElementID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.MSEBindingList list = new DTO.MSEBindingList();
            const string sprocname = "[ServiceEngine].[ListBindingsForBindingElementID]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.MSEBindings = new List<DTO.MSEBindingListElement>();
                            while (reader.Read())
                            {
                                DTO.MSEBindingListElement element = new DTO.MSEBindingListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.MSEBindings.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection in opened in this method
            }


            return list;
        }

    }
}
